package com.spring.mvc.export;

import java.net.URLEncoder;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.spring.mvc.model.Student;

/*
 * poiJAR 导出Excel
 */
public class ViewExcel extends AbstractExcelView{

	@SuppressWarnings({ "rawtypes", "deprecation" })
	@Override
    @RequestMapping("/excel")
	protected void buildExcelDocument(Map<String, Object> model,
			HSSFWorkbook workbook, HttpServletRequest request, HttpServletResponse response)
			throws Exception {
		    String excelName="用户信息.xls";
		    //设置response 方式，使执行此controller时候自动出现下载页面，而非直接使用excel打开
		    response.setContentType("APPLICATION/OCTET-STREAM");
		    response.setHeader("Content-Disposition", "attachment; filename="+ URLEncoder.encode(excelName, "UTF-8"));		
	        List stuList=(List) model.get("list");
	        //产生Excel表头
	        HSSFSheet sheet=workbook.createSheet("studentList");
	        HSSFRow header=sheet.createRow(0);//第0行
	        //产生标题列 
	        header.createCell((short) 0).setCellValue("name");
	        header.createCell((short) 1).setCellValue("sex");
	        header.createCell((short) 2).setCellValue("date");
	        header.createCell((short) 3).setCellValue("count");
	        HSSFCellStyle cellStyle=workbook.createCellStyle();
	        cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("mm/dd/yyyy"));
	        
	        //填充数据
	        int rowNum=1;
	        for(Iterator iter=stuList.iterator();iter.hasNext();){
	        	Student element=(Student) iter.next();
	        	HSSFRow row=sheet.createRow(rowNum++);
	        	row.createCell((short) 0).setCellValue(element.getName().toString());  
	        	row.createCell((short) 1).setCellValue(element.getSex().toString());
	        	row.createCell((short) 2).setCellValue(element.getDate().toString());  
                row.getCell((short) 2).setCellStyle(cellStyle);  
                row.createCell((short) 3).setCellValue(element.getCount());  
	        }
	        
	        //列总和计算
	        HSSFRow row=sheet.createRow(rowNum);
	        row.createCell((short) 0).setCellValue("TOTAL:");
	        //D2到D[rowNum]单元格起(count数据)  
	        String formula="SUM(D2:D"+rowNum+")";
	        row.createCell((short) 3).setCellFormula(formula);
	}
	
}
